#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
创建材料分类表迁移脚本
用于管理材料的层级分类，支持多级分类
"""

import sqlite3
import os
from datetime import datetime

def get_db_path():
    """获取数据库路径"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    backend_dir = os.path.dirname(current_dir)
    db_path = os.path.join(backend_dir, 'instance', 'dispatch_system.db')
    return db_path

def create_material_categories_table():
    """创建材料分类表"""
    db_path = get_db_path()
    
    if not os.path.exists(db_path):
        print(f"数据库文件不存在: {db_path}")
        return False
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查表是否已存在
        cursor.execute("""
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='material_categories'
        """)
        
        if cursor.fetchone():
            print("material_categories 表已存在，跳过创建")
            return True
        
        # 创建材料分类表
        cursor.execute("""
            CREATE TABLE material_categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(100) NOT NULL,              -- 分类名称
                code VARCHAR(50) UNIQUE NOT NULL,        -- 分类编码
                parent_id INTEGER,                       -- 父分类ID
                level INTEGER DEFAULT 1,                -- 分类层级
                path VARCHAR(500),                       -- 分类路径
                sort_order INTEGER DEFAULT 0,           -- 排序
                is_active BOOLEAN DEFAULT TRUE,         -- 是否启用
                description TEXT,                       -- 描述
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (parent_id) REFERENCES material_categories(id)
            )
        """)
        
        print("✅ material_categories 表创建成功")
        
        # 插入初始化数据
        insert_initial_categories(cursor)
        
        conn.commit()
        conn.close()
        
        print("✅ 材料分类表创建和初始化完成")
        return True
        
    except Exception as e:
        print(f"❌ 创建材料分类表失败: {str(e)}")
        if 'conn' in locals():
            conn.rollback()
            conn.close()
        return False

def insert_initial_categories(cursor):
    """插入初始化分类数据"""
    
    # 一级分类
    level1_categories = [
        ('电子元件', 'ELECTRONIC', None, 1, '/ELECTRONIC/', 1, True, '各类电子元器件'),
        ('机械配件', 'MECHANICAL', None, 1, '/MECHANICAL/', 2, True, '机械设备配件'),
        ('办公用品', 'OFFICE', None, 1, '/OFFICE/', 3, True, '日常办公用品'),
        ('清洁用品', 'CLEANING', None, 1, '/CLEANING/', 4, True, '清洁维护用品'),
        ('安全防护', 'SAFETY', None, 1, '/SAFETY/', 5, True, '安全防护用品'),
        ('工具设备', 'TOOLS', None, 1, '/TOOLS/', 6, True, '各类工具设备')
    ]
    
    # 插入一级分类
    cursor.executemany("""
        INSERT INTO material_categories 
        (name, code, parent_id, level, path, sort_order, is_active, description)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, level1_categories)
    
    # 获取一级分类ID
    cursor.execute("SELECT id, code FROM material_categories WHERE level = 1")
    level1_ids = {code: id for id, code in cursor.fetchall()}
    
    # 二级分类
    level2_categories = [
        # 电子元件子分类
        ('传感器', 'ELECTRONIC_SENSOR', level1_ids['ELECTRONIC'], 2, '/ELECTRONIC/SENSOR/', 1, True, '各类传感器'),
        ('控制器', 'ELECTRONIC_CONTROLLER', level1_ids['ELECTRONIC'], 2, '/ELECTRONIC/CONTROLLER/', 2, True, '控制器设备'),
        ('显示器', 'ELECTRONIC_DISPLAY', level1_ids['ELECTRONIC'], 2, '/ELECTRONIC/DISPLAY/', 3, True, '显示设备'),
        ('电源模块', 'ELECTRONIC_POWER', level1_ids['ELECTRONIC'], 2, '/ELECTRONIC/POWER/', 4, True, '电源相关模块'),
        ('连接器', 'ELECTRONIC_CONNECTOR', level1_ids['ELECTRONIC'], 2, '/ELECTRONIC/CONNECTOR/', 5, True, '各类连接器'),
        
        # 机械配件子分类
        ('轴承', 'MECHANICAL_BEARING', level1_ids['MECHANICAL'], 2, '/MECHANICAL/BEARING/', 1, True, '各类轴承'),
        ('齿轮', 'MECHANICAL_GEAR', level1_ids['MECHANICAL'], 2, '/MECHANICAL/GEAR/', 2, True, '齿轮传动件'),
        ('密封件', 'MECHANICAL_SEAL', level1_ids['MECHANICAL'], 2, '/MECHANICAL/SEAL/', 3, True, '密封圈、垫片等'),
        ('紧固件', 'MECHANICAL_FASTENER', level1_ids['MECHANICAL'], 2, '/MECHANICAL/FASTENER/', 4, True, '螺丝、螺母等'),
        ('皮带链条', 'MECHANICAL_BELT_CHAIN', level1_ids['MECHANICAL'], 2, '/MECHANICAL/BELT_CHAIN/', 5, True, '传动皮带链条'),
        
        # 办公用品子分类
        ('文具用品', 'OFFICE_STATIONERY', level1_ids['OFFICE'], 2, '/OFFICE/STATIONERY/', 1, True, '笔、纸等文具'),
        ('打印耗材', 'OFFICE_PRINTING', level1_ids['OFFICE'], 2, '/OFFICE/PRINTING/', 2, True, '墨盒、硒鼓等'),
        ('办公设备', 'OFFICE_EQUIPMENT', level1_ids['OFFICE'], 2, '/OFFICE/EQUIPMENT/', 3, True, '计算器、装订机等'),
        
        # 清洁用品子分类
        ('清洁剂', 'CLEANING_DETERGENT', level1_ids['CLEANING'], 2, '/CLEANING/DETERGENT/', 1, True, '各类清洁剂'),
        ('清洁工具', 'CLEANING_TOOLS', level1_ids['CLEANING'], 2, '/CLEANING/TOOLS/', 2, True, '拖把、抹布等'),
        ('垃圾袋', 'CLEANING_GARBAGE_BAG', level1_ids['CLEANING'], 2, '/CLEANING/GARBAGE_BAG/', 3, True, '各类垃圾袋'),
        
        # 安全防护子分类
        ('防护服', 'SAFETY_PROTECTIVE_CLOTHING', level1_ids['SAFETY'], 2, '/SAFETY/PROTECTIVE_CLOTHING/', 1, True, '工作服、防护服'),
        ('防护用具', 'SAFETY_PROTECTIVE_GEAR', level1_ids['SAFETY'], 2, '/SAFETY/PROTECTIVE_GEAR/', 2, True, '安全帽、手套等'),
        ('急救用品', 'SAFETY_FIRST_AID', level1_ids['SAFETY'], 2, '/SAFETY/FIRST_AID/', 3, True, '急救包、药品等'),
        
        # 工具设备子分类
        ('手动工具', 'TOOLS_HAND_TOOLS', level1_ids['TOOLS'], 2, '/TOOLS/HAND_TOOLS/', 1, True, '扳手、螺丝刀等'),
        ('电动工具', 'TOOLS_POWER_TOOLS', level1_ids['TOOLS'], 2, '/TOOLS/POWER_TOOLS/', 2, True, '电钻、切割机等'),
        ('测量工具', 'TOOLS_MEASURING', level1_ids['TOOLS'], 2, '/TOOLS/MEASURING/', 3, True, '卷尺、游标卡尺等')
    ]
    
    # 插入二级分类
    cursor.executemany("""
        INSERT INTO material_categories 
        (name, code, parent_id, level, path, sort_order, is_active, description)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """, level2_categories)
    
    print(f"✅ 插入了 {len(level1_categories)} 个一级分类和 {len(level2_categories)} 个二级分类")

def update_materials_table():
    """更新materials表，添加category_id字段"""
    db_path = get_db_path()
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # 检查是否已有category_id字段
        cursor.execute("PRAGMA table_info(materials)")
        columns = [column[1] for column in cursor.fetchall()]
        
        if 'category_id' not in columns:
            # 添加category_id字段
            cursor.execute("""
                ALTER TABLE materials 
                ADD COLUMN category_id INTEGER REFERENCES material_categories(id)
            """)
            print("✅ 为materials表添加了category_id字段")
        else:
            print("materials表已有category_id字段，跳过添加")
        
        conn.commit()
        conn.close()
        
        return True
        
    except Exception as e:
        print(f"❌ 更新materials表失败: {str(e)}")
        if 'conn' in locals():
            conn.rollback()
            conn.close()
        return False

def main():
    """主函数"""
    print("开始创建材料分类表...")
    
    success = True
    
    # 创建材料分类表
    if not create_material_categories_table():
        success = False
    
    # 更新materials表
    if success and not update_materials_table():
        success = False
    
    if success:
        print("\n🎉 材料分类表创建成功！")
        print("\n已完成：")
        print("1. ✅ 创建material_categories表")
        print("2. ✅ 插入层级分类数据")
        print("3. ✅ 为materials表添加category_id字段")
        print("\n下一步可以：")
        print("1. 创建分类管理API接口")
        print("2. 更新材料管理页面支持分类")
        print("3. 迁移现有材料数据到对应分类")
    else:
        print("\n❌ 材料分类表创建失败")

if __name__ == '__main__':
    main()